package com.jspxcms.core.repository;

import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;

import com.jspxcms.common.orm.Limitable;
import com.jspxcms.core.domain.OperationLog;
import com.jspxcms.core.repository.plus.OperationLogDaoPlus;

public interface OperationLogDao extends Repository<OperationLog, Integer>,
		OperationLogDaoPlus {
	public Page<OperationLog> findAll(Specification<OperationLog> spec,
			Pageable pageable);

	public List<OperationLog> findAll(Specification<OperationLog> spec,
			Limitable limitable);

	public OperationLog findOne(Integer id);

	public OperationLog save(OperationLog bean);

	public void delete(OperationLog bean);

	// --------------------

	@Modifying
	@Query("delete from OperationLog bean where bean.site.id in (?1)")
	public int deleteBySiteId(Collection<Integer> siteIds);

	@Modifying
	@Query("delete from OperationLog bean where bean.user.id in (?1)")
	public int deleteByUserId(Collection<Integer> siteIds);

	@Query(value = "select DATE_FORMAT(a.f_time,'%Y%m%d') as days," +
			"sum(case when a.f_name = 'opr.info.add' then 1 else 0 end) as 'opr.info.add'," +
			"sum(case when a.f_name = 'opr.comment.add' then 1 else 0 end) as 'opr.comment.add'," +
			"sum(case when a.f_name = 'opr.node.add' then 1 else 0 end) as 'opr.node.add' " +
			"from cms_operation_log a " +
			"WHERE a.f_time >=?1  AND a.f_time < ?2 AND a.f_site_id = ?3 " +
			"group by a.f_site_id,days", nativeQuery = true)
    List<Object[]> activityDegree(Date begin, Date end, Integer siteId);
}
